﻿Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class ThuChiCoDinhDAO
    Shared conn_str As String = "Data Source=.\sqlexpress;Initial Catalog=QuanLyThuChi;Integrated Security=True"
    Shared Function ThuCoDinhTheoIDUser(ByVal iduser As String) As DataTable
        Dim dt As New DataTable
        Dim query1 As String = "select tcd.id,tcd.so_tien,tknh.id,nh.Ten,tcd.id_danhmuc_thu,pltc.ten_loai,tcd.chi_tiet,tcd.ngay_thu,tcd.id_user_tao,usr.Fullname,tcd.id_user_thu,usr.Fullname,tcd.da_thu from ThuCoDinh tcd, Users usr, TaiKhoanNganHang tknh, NganHang nh, PhanLoaiThuChi pltc where tcd.id_user_tao = @id and tcd.id_danhmuc_thu = pltc.id and TKNH.MaNganHang = NH.MaNganHang and tcd.id_loai_tk = tknh.id and usr.MaUser = @maus"
        Try
            Dim adapter1 As New SqlDataAdapter(query1, conn_str)
            adapter1.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = iduser
            adapter1.SelectCommand.Parameters.Add("@maus", SqlDbType.Int).Value = iduser
            adapter1.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query1, conn_str)
        End Try
        Return dt
    End Function
    Shared Function GetAllChiCoDinh(ByVal iduser) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select ccd.id,ccd.so_tien,tknh.id,nh.Ten,ccd.id_danhmuc_chi,pltc.ten_loai,ccd.chi_tiet,ccd.ngay_chi,ccd.id_user_tao,usr.Fullname,ccd.id_user_chi,usr.Fullname,ccd.da_chi from ChiCoDinh ccd, Users usr, TaiKhoanNganHang tknh, NganHang nh, PhanLoaiThuChi pltc where ccd.id_user_tao = @id and ccd.id_danhmuc_chi = pltc.id and TKNH.MaNganHang = NH.MaNganHang and ccd.id_loai_tk = tknh.id and usr.MaUser = @maus"
        Try
            Dim adapter As New SqlDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = iduser
            adapter.SelectCommand.Parameters.Add("@maus", SqlDbType.Int).Value = iduser
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function

    Shared Function InsertThuCoDinh(ByVal tcd As ThuCoDinhDBO) As Integer
        Dim chuoisql As String = "insert into thucodinh(so_tien,id_loai_tk,id_danhmuc_thu,chi_tiet,ngay_thu,id_user_tao,id_user_thu,da_thu) values(@sotien,@idloaitk,@iddanhmuc,@chitiet,@ngaythu,@idusertao,@iduserthu,@dathu)"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim Command As SqlCommand = New SqlCommand(chuoisql, Ketnoi)
            Command.Parameters.Add("@sotien", SqlDbType.Money).Value = tcd.sotien
            Command.Parameters.Add("@idloaitk", SqlDbType.Int).Value = tcd.id_loai_tk
            Command.Parameters.Add("@iddanhmuc", SqlDbType.Int).Value = tcd.id_danhmuc
            Command.Parameters.Add("@chitiet", SqlDbType.NVarChar).Value = tcd.chitiet
            Command.Parameters.Add("@ngaythu", SqlDbType.Int).Value = tcd.ngaythu
            Command.Parameters.Add("@idusertao", SqlDbType.Int).Value = tcd.id_user_tao
            Command.Parameters.Add("@iduserthu", SqlDbType.Int).Value = tcd.id_user_thu
            Command.Parameters.Add("@dathu", SqlDbType.Int).Value = tcd.dathu
            Command.ExecuteNonQuery()
            Dim Command1 = New SqlCommand("Select @@IDENTITY", Ketnoi)
            Dim value As Integer = Integer.Parse(Command1.ExecuteScalar())
            Ketnoi.Close()
            Return value
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Shared Function DeleteThuCoDinh(ByVal id As Integer) As Integer
        Dim chuoisql As String = "delete from thucodinh where id = @id"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim command As SqlCommand = New SqlCommand(chuoisql, Ketnoi)
            command.Parameters.Add("id", SqlDbType.Int).Value = id
            command.ExecuteNonQuery()
            Ketnoi.Close()
            Return 1
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Shared Function SuaThuCodinh(ByVal dt As ThuCoDinhDBO, ByVal id As Integer) As Integer
        Dim chuoisql As String = "update thucodinh set so_tien = @sotien,id_loai_tk = @idloaitk,id_danhmuc_thu = @iddanhmuc,chi_tiet = @chitiet,ngay_thu = @ngaythu,id_user_tao = @idusertao,id_user_thu = @iduserthu,da_thu = @dathu where id = @id"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim command As SqlCommand = New SqlCommand(chuoisql, Ketnoi)
            command.Parameters.Add("@sotien", SqlDbType.Money).Value = dt.sotien
            command.Parameters.Add("@idloaitk", SqlDbType.Int).Value = dt.id_loai_tk
            command.Parameters.Add("@iddanhmuc", SqlDbType.Int).Value = dt.id_danhmuc
            command.Parameters.Add("@chitiet", SqlDbType.NVarChar).Value = dt.chitiet
            command.Parameters.Add("@ngaythu", SqlDbType.Int).Value = dt.ngaythu
            command.Parameters.Add("@idusertao", SqlDbType.Int).Value = dt.id_user_tao
            command.Parameters.Add("@iduserthu", SqlDbType.Int).Value = dt.id_user_thu
            command.Parameters.Add("@dathu", SqlDbType.Int).Value = dt.dathu
            command.Parameters.Add("@id", SqlDbType.Int).Value = id
            command.ExecuteNonQuery()
            Ketnoi.Close()
            Return 1
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Shared Function InsertChiCoDinh(ByVal ccd As ChiCoDinh) As Integer
        Dim chuoisql As String = "insert into chicodinh(so_tien,id_loai_tk,id_danhmuc_chi,chi_tiet,ngay_chi,id_user_tao,id_user_chi,da_chi) values(@sotien,@idloaitk,@iddanhmuc,@chitiet,@ngaychi,@idusertao,@iduserchi,@dachi)"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim Command As SqlCommand = New SqlCommand(chuoisql, Ketnoi)
            Command.Parameters.Add("@sotien", SqlDbType.Money).Value = ccd.sotien
            Command.Parameters.Add("@idloaitk", SqlDbType.Int).Value = ccd.id_loai_tk
            Command.Parameters.Add("@iddanhmuc", SqlDbType.Int).Value = ccd.id_danhmuc
            Command.Parameters.Add("@chitiet", SqlDbType.NVarChar).Value = ccd.chitiet
            Command.Parameters.Add("@ngaychi", SqlDbType.Int).Value = ccd.ngaychi
            Command.Parameters.Add("@idusertao", SqlDbType.Int).Value = ccd.id_user_tao
            Command.Parameters.Add("@iduserchi", SqlDbType.Int).Value = ccd.id_user_chi
            Command.Parameters.Add("@dachi", SqlDbType.Int).Value = ccd.dachi
            Command.ExecuteNonQuery()
            Dim Command1 = New SqlCommand("Select @@IDENTITY", Ketnoi)
            Dim value As Integer = Integer.Parse(Command1.ExecuteScalar())
            Ketnoi.Close()
            Return value
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Shared Function DeleteChiCoDinh(ByVal id As Integer) As Integer
        Dim chuoisql As String = "delete from chicodinh where id = @id"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim command As SqlCommand = New SqlCommand(chuoisql, Ketnoi)
            command.Parameters.Add("id", SqlDbType.Int).Value = id
            command.ExecuteNonQuery()
            Ketnoi.Close()
            Return 1
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Shared Function SuaChiCodinh(ByVal dt As ChiCoDinh, ByVal id As Integer) As Integer
        Dim chuoisql As String = "update chicodinh set so_tien = @sotien,id_loai_tk = @idloaitk,id_danhmuc_chi = @iddanhmuc,chi_tiet = @chitiet,ngay_chi = @ngaychi,id_user_tao = @idusertao,id_user_chi = @iduserchi,da_chi = @dachi where id = @id"
        Try
            Dim Ketnoi As SqlConnection = New SqlConnection(conn_str)
            Ketnoi.Open()
            Dim command As SqlCommand = New SqlCommand(chuoisql, Ketnoi)
            command.Parameters.Add("@sotien", SqlDbType.Money).Value = dt.sotien
            command.Parameters.Add("@idloaitk", SqlDbType.Int).Value = dt.id_loai_tk
            command.Parameters.Add("@iddanhmuc", SqlDbType.Int).Value = dt.id_danhmuc
            command.Parameters.Add("@chitiet", SqlDbType.NVarChar).Value = dt.chitiet
            command.Parameters.Add("@ngaychi", SqlDbType.Int).Value = dt.ngaychi
            command.Parameters.Add("@idusertao", SqlDbType.Int).Value = dt.id_user_tao
            command.Parameters.Add("@iduserchi", SqlDbType.Int).Value = dt.id_user_chi
            command.Parameters.Add("@dachi", SqlDbType.Int).Value = dt.dachi
            command.Parameters.Add("@id", SqlDbType.Int).Value = id
            command.ExecuteNonQuery()
            Ketnoi.Close()
            Return 1
        Catch ex As Exception
            Return -1
        End Try
    End Function
End Class
